package com.dao.impl;

import com.dao.ParticipantInfoDAO;
import com.entity.MedalInfo;
import com.entity.ParticipantInfo;
import util.DBUtil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ParticipantInfoDAOImpl implements ParticipantInfoDAO {
    @Override
    public int insert(ParticipantInfo entity) throws SQLException {
        String sql = "INSERT INTO participant_info(participant_id,country_code,participant_name,participant_birth,participant_high,participant_weight,participant_img_uir,participant_brief) values(?,?,?,?,?,?,?,?)";
        try(
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            pstmt.setObject(1, entity.getParticipantId());
            pstmt.setObject(2,entity.getCountryCode());
            pstmt.setObject(3,entity.getParticipantName());
            pstmt.setObject(4,entity.getParticipantBirth());
            pstmt.setObject(5,entity.getParticipantHigh());
            pstmt.setObject(6,entity.getParticipantWeight());
            pstmt.setObject(7,entity.getParticipantImgUir());
            pstmt.setObject(8,entity.getParticipantBrief());
            return pstmt.executeUpdate();
        }
    }

    @Override
    public int updateById(ParticipantInfo entity) throws SQLException {
        String sql = "update participant_info set country_code=?,participant_name=?,participant_birth=?,participant_high=?,participant_weight=?,participant_img_uir=?,participant_brief=? where participant_id=?";
        try(
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            pstmt.setObject(8, entity.getParticipantId());
            pstmt.setObject(1,entity.getCountryCode());
            pstmt.setObject(2,entity.getParticipantName());
            pstmt.setObject(3,entity.getParticipantBirth());
            pstmt.setObject(4,entity.getParticipantHigh());
            pstmt.setObject(5,entity.getParticipantWeight());
            pstmt.setObject(6,entity.getParticipantImgUir());
            pstmt.setObject(7,entity.getParticipantBrief());
            return pstmt.executeUpdate();
        }
    }

    @Override
    public int deleteById(Serializable id) throws SQLException {
        String sql = "delete from participant_info  where participant_id=?";
        try(
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            pstmt.setObject(1, id);
            return pstmt.executeUpdate();
        }
    }

    @Override
    public ParticipantInfo selectById(Serializable id) throws SQLException {
        String sql = "select * from participant_info  where participant_id=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setObject(1, id);
            rs = pstmt.executeQuery();
            if(rs.next()){
                ParticipantInfo entity = new ParticipantInfo();
                entity.setParticipantId(rs.getInt("participant_id"));
                //如果该字段可能为空
                entity.setCountryCode(rs.getString("country_code"));
                entity.setParticipantName(rs.getString("participant_name"));
                entity.setParticipantBirth(rs.getDate("participant_birth"));
                entity.setParticipantHigh(rs.getInt("participant_high"));
                entity.setParticipantWeight(rs.getDouble("participant_weight"));
                entity.setParticipantImgUir(rs.getString("participant_img_uir"));
                entity.setParticipantBrief(rs.getString("participant_brief"));
                return entity;
            }else{
                throw new SQLException("没有找到对应的记录");
            }
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }

    @Override
    public List<ParticipantInfo> select(int index, int len) throws SQLException {
        String sql = "select * from participant_info  order by participant_id limit ?,?";//只要是返回多条记录,一定记得+order by子句
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setObject(1, index);
            pstmt.setObject(2, len);
            rs = pstmt.executeQuery();
            List<ParticipantInfo> list = new ArrayList<>();
            while (rs.next()) {
                ParticipantInfo entity = new ParticipantInfo();
                entity.setParticipantId(rs.getInt("participant_id"));
                //如果该字段可能为空
                entity.setCountryCode(rs.getString("country_code"));
                entity.setParticipantName(rs.getString("participant_name"));
                entity.setParticipantBirth(rs.getDate("participant_birth"));
                entity.setParticipantHigh(rs.getInt("participant_high"));
                entity.setParticipantWeight(rs.getDouble("participant_weight"));
                entity.setParticipantImgUir(rs.getString("participant_img_uir"));
                entity.setParticipantBrief(rs.getString("participant_brief"));
                list.add(entity);
            }
            return list;
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }

    @Override
    public long count() throws SQLException {
        String sql = "select count(*) from participant_info";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if(rs.next()){
                return rs.getLong(1);
            }else{
                throw new SQLException("没有找到对应的记录");
            }
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }
}
